import pandas as pd

from app_config import get_pro, get_engine
import os


class O000688(object):
    """Calculate the volatility of the last one year"""
    one_year_ago: str = None
    zero_year_ago: str = None

    """file output path"""
    folder_path: str = None
    """ constituent stock"""
    date_000688_kc50: str = None

def calc_kc50(o_000688: O000688 | None = None):
    one_year_ago = o_000688.one_year_ago
    zero_year_ago = o_000688.zero_year_ago
    folder_path = o_000688.folder_path
    date_000688_kc50 = o_000688.date_000688_kc50

    print(f"""
        folder_path: {"     " + folder_path}
        zero_year_ago: {"   " + zero_year_ago}
        one_year_ago: {"    " + one_year_ago}
        date_000688_kc50: {"" + date_000688_kc50}
        """)

    if not os.path.exists(folder_path):
        os.makedirs(folder_path)
        print(f"文件夹 '{folder_path}' 创建成功")

    engine = get_engine()

    df_kcb = get_pro().stock_basic(market='科创板', list_status='L', fields='ts_code,symbol,name,area,industry,list_date')

    df_kcb_oneYear = df_kcb[df_kcb['list_date'] <= one_year_ago]

    ts_codes_oneYear = df_kcb_oneYear['ts_code'].tolist()

    # 构建查询语句
    sql_daily = f"""
    SELECT ts_code,trade_date, amount FROM `daily`
    WHERE ts_code IN ({','.join(f"'{code}'" for code in ts_codes_oneYear)})
    AND trade_date >= '{one_year_ago}'
    AND trade_date <= '{zero_year_ago}'
    """

    # 执行查询并将结果转换为DataFrame
    df_daily = pd.read_sql_query(sql_daily, engine)
    df_daily['amount'] = df_daily['amount'] / 1000
    df_daily_avgAmount = df_daily.groupby('ts_code')['amount'].mean().reset_index()
    df_daily_avgAmount.columns = ['ts_code', '日均成交额']
    df_kcb_oneYear_avgAmount = pd.merge(df_kcb_oneYear, df_daily_avgAmount, on='ts_code', how='left')

    # 构建查询语句
    sql_daily_basic = f"""
        SELECT ts_code,trade_date, total_mv FROM `daily_basic`
        WHERE ts_code IN ({','.join(f"'{code}'" for code in ts_codes_oneYear)})
        AND trade_date >= '{one_year_ago}'
        AND trade_date <= '{zero_year_ago}'
        """
    # 执行查询并将结果转换为DataFrame
    df_dailyBasic = pd.read_sql_query(sql_daily_basic, engine)
    df_dailyBasic['total_mv'] = df_dailyBasic['total_mv'] / 10000
    df_dailyBasic_avgTotalMv = df_dailyBasic.groupby('ts_code')['total_mv'].mean().reset_index()
    df_dailyBasic_avgTotalMv.columns = ['ts_code', '日均总市值']
    df_kcb_oneYear_avgAmount_avgTotalMv = pd.merge(df_kcb_oneYear_avgAmount, df_dailyBasic_avgTotalMv, on='ts_code', how='left')

    # 按 日均成交额 列降序排序
    df_kcb_oneYear_avgAmountSort_avgTotalMv = df_kcb_oneYear_avgAmount_avgTotalMv.sort_values(by='日均成交额', ascending=False).reset_index(drop=True)
    df_kcb_oneYear_avgAmountSort_avgTotalMv['i_avgAmount'] = df_kcb_oneYear_avgAmountSort_avgTotalMv.index
    # 获取前 90% 的数据
    num_rows = len(df_kcb_oneYear_avgAmountSort_avgTotalMv)
    df_kcb_oneYear_avgAmountTop90P_avgTotalMv = df_kcb_oneYear_avgAmountSort_avgTotalMv.head(int(num_rows * 0.9))

    df_kcb_oneYear_avgAmountTop90P_avgTotalMvSort = df_kcb_oneYear_avgAmountTop90P_avgTotalMv.sort_values('日均总市值', ascending=False).reset_index(drop=True)
    df_kcb_oneYear_avgAmountTop90P_avgTotalMvSort['i_totalMv'] = df_kcb_oneYear_avgAmountTop90P_avgTotalMvSort.index
    df_kcb_oneYear_avgAmountTop90P_avgTotalMvTop120 = df_kcb_oneYear_avgAmountTop90P_avgTotalMvSort.head(120)
    # df_kcb_oneYear_avgAmountTop90P_avgTotalMvTop120.to_excel(str_filePre + '科创50.xlsx', index=True)

    df_930955 = get_pro().index_weight(index_code='000688.SH', start_date=date_000688_kc50, end_date=date_000688_kc50)
    df_930955.rename(columns={'con_code': 'ts_code'}, inplace=True)
    df_930955.rename(columns={'trade_date': 'list_date'}, inplace=True)
    df_930955['symbol'] = df_930955['ts_code'].str.split('.').str[0]
    df_930955.drop(columns=['weight'], inplace=True)
    df_930955.to_excel(folder_path + "/" + date_000688_kc50 + '-000688.xlsx')

    # 获取 dataframe1 中的所有列
    columns_dataframe1 = df_kcb_oneYear_avgAmountTop90P_avgTotalMvTop120.columns

    # 创建缺失列并填充为 '*'
    for column in columns_dataframe1:
        if column not in df_930955.columns:
            df_930955[column] = '**'

    # 将 dataframe2 的列顺序调整为与 dataframe1 一致
    df_930955 = df_930955[columns_dataframe1]

    # 追加 dataframe2 到 dataframe1
    result = pd.concat([df_kcb_oneYear_avgAmountTop90P_avgTotalMvTop120, df_930955], ignore_index=True)

    result.to_excel(folder_path + '_.xlsx')


if __name__ == '__main__':
    _2025Q1 = O000688()
    _2024Q1 = O000688()
    _2023Q1 = O000688()
    _2022Q1 = O000688()

    _2024Q3 = O000688()
    _2023Q3 = O000688()
    _2022Q3 = O000688()
    _2021Q3 = O000688()

    _2024Q4 = O000688()
    _2023Q4 = O000688()
    _2022Q4 = O000688()
    _2021Q4 = O000688()

    _2024Q2 = O000688()
    _2023Q2 = O000688()
    _2022Q2 = O000688()
    _2021Q2 = O000688()

    _2024Q4.zero_year_ago = '20241031'
    _2023Q4.zero_year_ago = '20231031'
    _2022Q4.zero_year_ago = '20221031'

    _2024Q4.one_year_ago = '20231101'
    _2023Q4.one_year_ago = '20221101'
    _2022Q4.one_year_ago = '20211101'

    _2024Q4.date_000688_kc50 = '20250127'
    _2023Q4.date_000688_kc50 = '20240131'
    _2022Q4.date_000688_kc50 = '20230131'

    _2024Q3.one_year_ago = '20230801'
    _2023Q3.one_year_ago = '20220801'
    _2022Q3.one_year_ago = '20210801'

    _2024Q3.zero_year_ago = '20240731'
    _2023Q3.zero_year_ago = '20230731'
    _2022Q3.zero_year_ago = '20220731'

    _2024Q3.date_000688_kc50 = '20241031'
    _2023Q3.date_000688_kc50 = '20231031'
    _2022Q3.date_000688_kc50 = '20221031'


    _2024Q2.one_year_ago = '20230501'
    _2023Q2.one_year_ago = '20220501'

    _2024Q2.zero_year_ago = '20240430'
    _2023Q2.zero_year_ago = '20230430'

    _2024Q2.date_000688_kc50 = '20240628'
    _2023Q2.date_000688_kc50 = '20230731'

    _2025Q1.zero_year_ago = '20250131'
    _2024Q1.zero_year_ago = '20240131'
    _2023Q1.zero_year_ago = '20230131'

    _2025Q1.one_year_ago = '20240201'
    _2024Q1.one_year_ago = '20230201'
    _2023Q1.one_year_ago = '20220201'

    _2025Q1.date_000688_kc50 = '20250127'
    _2024Q1.date_000688_kc50 = '20240430'
    _2023Q1.date_000688_kc50 = '20230428'

    _2024Q4.folder_path = 'zfile/科创50_2024Q4'
    _2023Q4.folder_path = 'zfile/科创50_2023Q4'
    _2022Q4.folder_path = 'zfile/科创50_2022Q4'
    _2024Q3.folder_path = 'zfile/科创50_2024Q3'
    _2023Q3.folder_path = 'zfile/科创50_2023Q3'
    _2022Q3.folder_path = 'zfile/科创50_2022Q3'
    _2024Q2.folder_path = 'zfile/科创50_2024Q2'
    _2023Q2.folder_path = 'zfile/科创50_2023Q2'

    _2025Q1.folder_path = 'zfile/科创50_2025Q1'
    _2024Q1.folder_path = 'zfile/科创50_2024Q1'
    _2023Q1.folder_path = 'zfile/科创50_2023Q1'

    calc_kc50(_2025Q1)
    # calc_kc50(_2024Q1)
    # calc_kc50(_2025Q1)
    # calc_kc50(_2023Q1)
    # calc_kc50(_2022Q1)
    # # calc_kc50(_2021Q1)
    # calc_kc50(_2024Q3)
    # calc_kc50(_2023Q3)
    # calc_kc50(_2022Q3)
    # calc_kc50(_2021Q3)
    # calc_kc50(_2024Q4)
    # calc_kc50(_2023Q4)
    # calc_kc50(_2022Q4)
    # calc_kc50(_2021Q4)
    # calc_kc50(_2024Q2)
    # calc_kc50(_2023Q2)
    # calc_kc50(_2022Q2)
    # calc_kc50(_2021Q2)
